package com.mobimap.nhom9.dao;

import java.util.ArrayList;
import java.util.List;

import android.database.sqlite.SQLiteDatabase;

import com.mobimap.nhom9.dao.table.PlaceTable;
import com.mobimap.nhom9.dto.LocationDTO;
import com.mobimap.nhom9.dto.PlaceExDTO;

public class PlaceDAO extends OneKeyTableDAO<PlaceExDTO> {

	private static final PlaceTable info = new PlaceTable();

	public PlaceDAO(SQLiteDatabase db) {
		super(db, info);

	}
	/*
	public List<PlaceExDTO> timDichVu(String keyword) {
		String param = SqlHelper.FormatParamSql(keyword);
		String selection = String
				.format("Place.Address LIKE '%%%s%%' OR '%s' LIKE ('%%' || Place.Address || '%%') "
						+ "OR Place.Fullname LIKE '%%%s%%' OR '%s' LIKE ('%%' || Place.Fullname || '%%')",
						param, param, param, param);
		List<PlaceExDTO> dtos = getDTOs(selection);
		return dtos;
	}*/
	/**
	 * get all Places by Keyword (search by Address, Fullname, Phone)
	 * @param keyword
	 * @return
	 */
	public List<PlaceExDTO> timDichVu(String keyword) {
		List<String> lstQueryTemp = new ArrayList<String>();
		lstQueryTemp.add("SELECT Place.* FROM Place, KeyPlace WHERE Place.ID=KeyPlace.PlaceId" +
				" AND KeyPlace.Address LIKE '%%%s%%'");
		lstQueryTemp.add("SELECT Place.* FROM Place, KeyPlace WHERE Place.ID=KeyPlace.PlaceId" +
				" AND '%s' LIKE ('%%' || KeyPlace.Address || '%%')");
		
		lstQueryTemp.add("SELECT Place.* FROM Place, KeyPlace WHERE Place.ID=KeyPlace.PlaceId" +
				" AND KeyPlace.Fullname LIKE '%%%s%%'");
		lstQueryTemp.add("SELECT Place.* FROM Place, KeyPlace WHERE Place.ID=KeyPlace.PlaceId" +
				" AND '%s' LIKE ('%%' || KeyPlace.Fullname || '%%')");
		
		lstQueryTemp.add("SELECT Place.* FROM Place, KeyPlace WHERE Place.ID=KeyPlace.PlaceId" +
				" AND Place.Phone LIKE '%%%s%%'");
		
		
		String param = StringHelper.FormatParamSql(keyword);
		List<PlaceExDTO> lstPlaces = new ArrayList<PlaceExDTO>();
		for (String queryTemp : lstQueryTemp) {
			String query = String.format(queryTemp, param);
			lstPlaces.addAll(QueryHelper.rawQuery(this.database, query, null, this.table));
		}
		return lstPlaces;
	}
	/**
	 * get list places from place table by type id and distance ascending
	 * @param placeTypeId
	 * @param location
	 * @param limit
	 * @return
	 */
	public List<PlaceExDTO> getPlacesByTypeAndDistance(int placeTypeId, LocationDTO location, int limit)	{
		String query = String.format("SELECT Place.*, ((Place.xLocation-%s)*(Place.xLocation-%s) + (Place.yLocation-%s)*(Place.yLocation-%s)) as Distance" +
				" FROM Place WHERE Place.Type=%s ORDER BY Distance ASC LIMIT %s", location.getLatitude(), location.getLatitude(), location.getLongitude(), location.getLongitude(),
				placeTypeId, limit);
		return QueryHelper.rawQuery(this.database, query, null, this.table);
	}
}
